/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package HumanCentredSystemDesign;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.text.SimpleDateFormat;
import java.text.DateFormat;
/**
 *
 * @author Martin and Tim
 */
public class Connect {
    
    Connection conn = null;
    
   public void establishConnection(){
       try
       {
           String userName = "hcsd3";
           String password = "teamstruth";
           String url = "jdbc:mysql://stusql.dcs.shef.ac.uk/hcsd3";
           Class.forName ("com.mysql.jdbc.Driver").newInstance ();
           conn = DriverManager.getConnection (url, userName, password);
       }
       catch (Exception e)
       {
           System.err.println ("Cannot connect to database server: " + e.toString());
       }

   }

   public void closeConnection(){
       if (conn != null)
       {
           try
           {
               conn.close ();
           }
           catch (Exception e) {
               System.out.println("Error Closing Connection: " + e.toString());
           }
       }
   }

   public void updateDatabase(String InsertionString){

       try{
            Statement st = this.conn.createStatement();
            int val = st.executeUpdate(InsertionString);
        }
        catch(SQLException s){
            System.out.println("SQL statement is not executed!" + s.getMessage());
        }
   }

       

       
   public ArrayList<Borrower> getBorrowersFromDatabase(String libraryMemberID, String forename, String surname, String emailAddress){
   // An Improved Version of Select Borrower from database, now includes 
    Statement stmt = null;	
    ArrayList<Borrower> borrowerList = new ArrayList<Borrower>();


    if ("".equals(libraryMemberID)){
        libraryMemberID = "%";
    }
    if ("".equals(forename)){
        forename = "%";
    }
    if ("".equals(surname)){
        surname = "%";
    }
    if ("".equals(emailAddress)){
        emailAddress = "%";
    }

    try{
        stmt = this.conn.createStatement(); 
        ResultSet res = stmt.executeQuery("SELECT * From Borrowers Where libraryMemberID LIKE '"+libraryMemberID+"' AND forename LIKE '"+forename+"' AND surname LIKE '"+surname+"' AND emailAddress LIKE '"+emailAddress+"'");
        while (res.next()) {
            Borrower myBorrower = new Borrower(res.getString("libraryMemberID"),res.getString("forename"), res.getString("surname"),res.getString("emailAddress"));
            borrowerList.add(myBorrower);
        }

    }
    catch(SQLException ex){
        System.out.println(ex.toString());
    }
    return borrowerList;

   }

   public ArrayList<Loan> getBorrowersLoans(Borrower oldBorrower){

       Statement stmt = null;	
       ArrayList<Loan> loanList = new ArrayList<Loan>();
       try{
        stmt = this.conn.createStatement();
        ResultSet res = stmt.executeQuery(
            "SELECT *" +
            " FROM " + 
            " `hcsd3`.`Borrowers` INNER JOIN `hcsd3`.`Loans` ON Borrowers.`libraryMemberID` = Loans.`libraryMemberID` " +
            " INNER JOIN `hcsd3`.`Copies` on Loans.deweyIndex = Copies.DeweyIndex LEFT OUTER JOIN `hcsd3`.`Items` on Items.itemIdentifier = Copies.itemIdentifier "+
            " LEFT OUTER JOIN `hcsd3`.`Periodicals` on Copies.itemIdentifier = ISSN "+
            " LEFT OUTER JOIN `hcsd3`.`Books` on Copies.itemIdentifier = ISBN" +
            " WHERE  " +
            " `hcsd3`.`Borrowers`.`libraryMemberID` = "+ oldBorrower.getBorrowerID()+""+
            " ;"                   
        );

        while (res.next()) { // Loop Through all loans 
            Loan myLoan = new Loan(); // Create a new loan
            if ("".equals(res.getString("isbn")))
            {
               myLoan.setIssueDate(res.getDate("issueDate"));
               myLoan.setDeweyIndex(res.getString("deweyIndex"));
               myLoan.setLibraryMemberID( res.getString("libraryMemberID"));
               myLoan.setLoanPeriod(res.getInt("loanPeriod"));                  
            }
            else
            {
                myLoan.setIssueDate(res.getDate("issueDate"));
                myLoan.setDeweyIndex(res.getString("deweyIndex"));
                myLoan.setLibraryMemberID(res.getString("libraryMemberID"));
                myLoan.setLoanPeriod(res.getInt("loanPeriod"));
            }
            loanList.add(myLoan);
        }
    }
    catch(SQLException ex){
        System.out.println(ex.toString());
    }  
       return loanList;  
   }

   public ArrayList<Item> getBooksFromDatabase(String _ISBN, String _title, String _author, String _publisher, String _date){
       ArrayList<Item> bookList = new ArrayList<Item>();

       Statement stmt = null;

       if ("".equals(_ISBN)){
        _ISBN = "%";
        }
       if ("".equals(_title)){
        _title = "%";
        }
        if ("".equals(_author)){
        _author = "%";
        }
       if ("".equals(_publisher)){
        _publisher = "%";
        }  
       if ("".equals(_date)){
        _date = "%";
        }
       try{
        stmt = this.conn.createStatement(); 
        String myString =   " SELECT"+
                " *" +
                " FROM" +
                " `hcsd3`.`Books`"+
                " Inner JOIN `hcsd3`.`Items` on Items.itemIdentifier = Books.isbn"+
                " WHERE isbn LIKE '"+ _ISBN +"' AND"+
                " title LIKE '"+ _title +"' AND "+
                " author LIKE '"+ _author +"' AND "+
                " publisher LIKE '"+ _publisher + "' AND "+
                " date LIKE '"+ _date +"'"+
                ";";
        ResultSet res = stmt.executeQuery(""+ myString    + "");
        while (res.next()) {
            Book myBook = new Book();

            myBook.setItemIdentifier(res.getString("isbn"));
            myBook.setPublisher(res.getString("publisher"));
            myBook.setTitle(res.getString("title"));
            myBook.setAuthor(res.getString("author"));
            myBook.setPublicationDate(res.getDate("date"));

            bookList.add(myBook);

        }
    }
    catch(SQLException ex){
        System.out.println(ex.toString());
    }

       return bookList;

   }

   public ArrayList<Item> getPeriodicalsFromDatabase (String _ISSN, String _title, String _publisher,String _date, String _volume, String _number){

       ArrayList<Item> periodicalList = new ArrayList<Item>();

       Statement stmt = null;

       if ("".equals(_ISSN)){
        _ISSN = "%";
        }
       if ("".equals(_title)){
        _title = "%";
        }

       if ("".equals(_publisher)){
        _publisher = "%";
        }  
       if ("".equals(_date)){
        _date = "%";
        }
         if ("".equals(_volume)){
        _volume = "%";
        }
          if ("".equals(_number)){
        _number = "%";
        } 

       try{
        stmt = this.conn.createStatement(); 


        String myString =   " SELECT"+
                " *" +
                " FROM" +
                " `hcsd3`.`Periodicals`"+
                " Inner JOIN `hcsd3`.`Items` on Items.itemIdentifier = Periodicals.issn"+
                " WHERE issn LIKE '"+ _ISSN +"' AND"+
                " title LIKE '"+ _title +"' AND "+
                " volume LIKE '"+ _volume +"' AND "+
                " number LIKE '"+ _number +"' AND "+
                " publisher LIKE '"+ _publisher + "' AND "+
                " date LIKE '"+ _date +"'"+
                ";";
        ResultSet res = stmt.executeQuery(""+ myString + "");
        while (res.next()) {
            Periodical myPeriodical = new Periodical(res.getString("issn"),Integer.parseInt(res.getString("number")),res.getDate("date"),res.getString("publisher"),res.getString("title"),Integer.parseInt(res.getString("number")));
            periodicalList.add(myPeriodical);
        }
    }
    catch(SQLException ex){
        System.out.println(ex.toString() + ex.getMessage().toString());
    }

       return periodicalList;

   }

   public ArrayList<Copy> getItemCopiesFromDatabase(String deweyIndex, String itemIdentifier){

    Statement stmt = null;
    ArrayList<Copy> copyList = new ArrayList<Copy>();
    String referenceOnly = "%";


    if ("".equals(deweyIndex)){
        deweyIndex = "%";
    }
    if ("".equals(itemIdentifier)){
        itemIdentifier = "%";
    }

    try{
        stmt = this.conn.createStatement();
        ResultSet res = stmt.executeQuery("SELECT * From Copies Where deweyIndex LIKE '"+deweyIndex+"' AND referenceOnly LIKE '"+referenceOnly+"' AND itemIdentifier LIKE '"+itemIdentifier+"'");

        while (res.next()) {
            Copy myCopy = new Copy();
            myCopy.setDeweyIndex(res.getString("deweyIndex"));
            String tiny2Bool = res.getString("referenceOnly");
            if (tiny2Bool.equals("1")) {
                myCopy.setReferenceOnly(true);
            }
            else {
                myCopy.setReferenceOnly(false);
            }
            myCopy.setItemIdentifier(res.getString("itemIdentifier"));
            copyList.add(myCopy);
        }

    }
    catch(SQLException ex){
        System.out.println(ex.toString());
    }
    return copyList;

   }

   public ArrayList<Loan> getLoansFromDatabase(String libraryMemberID, String deweyIndex){
    Statement stmt = null;
    ArrayList<Loan> loanList = new ArrayList<Loan>();
    String issueDate = "%";
    String loanPeriod = "%";


    if ("".equals(libraryMemberID)){
        libraryMemberID = "%";
    }
    if ("".equals(deweyIndex)){
        deweyIndex = "%";
    }

    try{
        stmt = this.conn.createStatement();
        ResultSet res = stmt.executeQuery("SELECT * From Loans Where issueDate Like '"+issueDate+"' AND libraryMemberID LIKE '"+libraryMemberID+"' AND deweyIndex LIKE '"+deweyIndex+"' AND loanPeriod LIKE '"+loanPeriod+"'");

        while (res.next()) {
            Loan myLoan = new Loan();
            myLoan.setIssueDate(res.getDate("issueDate"));
            myLoan.setLibraryMemberID(res.getString("libraryMemberID"));
            myLoan.setDeweyIndex(res.getString("deweyIndex"));
            myLoan.setLoanPeriod(res.getInt("loanPeriod"));
            loanList.add(myLoan);
        }

    }
    catch(SQLException ex){
        System.out.println(ex.toString());
    }
    return loanList;

   }

   public ArrayList<Reservation> getReservationsFromDatabase(String itemIdentifier, String libraryMemberID){

    Statement stmt = null;
    ArrayList<Reservation> reservationList = new ArrayList<Reservation>();

    if ("".equals(itemIdentifier)){
        itemIdentifier = "%";
    }
    if ("".equals(libraryMemberID)){
        libraryMemberID = "%";
    }

    try{
        stmt = this.conn.createStatement();
        String str = "SELECT * From Reservations Where itemIdentifier LIKE '"+itemIdentifier+"' AND libraryMemberID LIKE '"+libraryMemberID+"'";
        ResultSet res = stmt.executeQuery(str);
        while (res.next()) {
            Reservation myReservation = new Reservation();
            myReservation.setReservationDate(res.getDate("reservationDate"));
            myReservation.setItemIdentifier(res.getString("itemIdentifier"));
            myReservation.setLibraryMemberID(res.getString("libraryMemberID"));
            reservationList.add(myReservation);
        }

    }
    catch(SQLException ex){
        System.out.println(ex.toString());
    }

    return reservationList;

   }

    public void InsertLoanToDatabase(Loan _loan){          
        String insertString = "" + 
                "INSERT INTO `hcsd3`.`Loans` "+
                "(`issueDate`, `libraryMemberID`, `deweyIndex`, `loanPeriod`)"+
                "VALUES ('" + new SimpleDateFormat("yyyy-MM-dd").format(_loan.getIssueDate()) + "', '" + _loan.getLibraryMemberID() +"', '"+_loan.getDeweyIndex() +"',"+_loan.getLoanPeriod() +" );";
        this.updateDatabase(insertString);
    }

    public void DeleteLoanFromDatabase(Loan _loan){
        String deletionString = 
                "DELETE FROM `hcsd3`.`Loans` WHERE `libraryMemberID`='"+ _loan.getLibraryMemberID()+"' and`deweyIndex`='"+ _loan.getDeweyIndex()+"';";
        this.updateDatabase(deletionString);
    }

    public void InsertReservationToDatabase(Reservation _reservation)
    {
        DateFormat formatter;
        formatter = new SimpleDateFormat("yyyy-MM-dd");
        String myDate = formatter.format(_reservation.getReservationDate());


        String insertString = "INSERT INTO `hcsd3`.`Reservations`"+
                    "(`reservationDate`, `itemIdentifier`, `libraryMemberID`)"+
                    "VALUES ('" + myDate +"', '"+ _reservation.getItemIdentifier()+"', '" + _reservation.getlibraryMemberID() +"');" ;
        this.updateDatabase(insertString);

    }

    public void CancelReservationFromDatabase(Reservation _reservation)
    {

         String insertString = "DELETE FROM `hcsd3`.`Reservations`"+
                 "WHERE `libraryMemberID`='"+ _reservation.getlibraryMemberID() +"' and"+
                 "`itemIdentifier`='"+ _reservation.getItemIdentifier() + "';";
                 this.updateDatabase(insertString);
    }

    public void RecallLoanInDatabase(Loan _loan){
        String insertString = "UPDATE `hcsd3`.`Loans`"+
                "SET `loanPeriod`='"+_loan.getLoanPeriod()+"' "+
                "WHERE `libraryMemberID`='"+_loan.getLibraryMemberID()+"' and`deweyIndex`='"+_loan.getDeweyIndex()+"';";
        this.updateDatabase(insertString);

    }

        
        
}


